package com.mmy.dao.train;

import java.util.Date;
import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.mmy.domain.train.VTrainPlan;

public interface VTrainPlanRepository extends JpaRepository<VTrainPlan, Integer> {

	/*
	 * 根据id查找
	 */
	@Query(value = "select *  from vTrainPlan where iPlanID = :iPlanID", nativeQuery = true)
	VTrainPlan findByID(@Param("iPlanID") int iPlanID);

	/*
	 * 根据name查找
	 */
	@Query(value = "select *  from vTrainPlan where cTrainName like %:strTrainName%", nativeQuery = true)
	List<VTrainPlan> findByName(@Param("strTrainName") String strTrainName);

	/*
	 * 根据ID查找
	 */
	@Query(value = "select *  from vTrainPlan where iTrainID = :iTrainID", nativeQuery = true)
	List<VTrainPlan> findByTrainID(@Param("iTrainID") int iTrainID);

	/**
	 * 函数功能说明： 跳转进入前台火车票页面 火车票推荐模块 推荐由北京出发的火车票信息 国内
	 *
	 *
	 *
	 * 出发时间大于现在时间至少一天
	 *
	 * 按照 到达地分组 价格最少的航班
	 * 
	 *
	 * @param 参数说明
	 * @return 返回值说明 jsp页面
	 * @throws 异常处理说明
	 * @author 创建人Hanxl
	 * @date 创建日期2021.02.24 修改时间2021.03.17
	 */
	@Query(value = "SELECT a.* FROM vTrainPlan a INNER JOIN ( SELECT iZoneInID, MIN( fPrice ) AS fMinPrice  FROM vTrainPlan "
			+ " WHERE DateDIFF(dStartDate,now()) between 1 and 90  GROUP BY iZoneInID ) b ON a.iZoneInID = b.iZoneInID "
			+ "AND a.fPrice = b.fMinPrice and a.iZoneInID<900000 and  a.cZoneOutName like %?1% limit 10", nativeQuery = true)
	List<VTrainPlan> findByZoneOutName(String strZoneOutName);

	/**
	 * 函数功能说明： 跳转进入前台火车票页面 火车票推荐模块 国外
	 * 
	 *
	 *
	 * 出发时间大于现在时间至少一天
	 *
	 * 按照 到达地分组 价格最少的航班
	 * 
	 *
	 * @param 参数说明
	 * @return 返回值说明 jsp页面
	 * @throws 异常处理说明
	 * @author 创建人Hanxl
	 * @date 创建日期2021.02.24 修改时间2021.03.17
	 */
	@Query(value = "SELECT a.* FROM vTrainPlan a INNER JOIN ( SELECT iZoneInID, MIN( fPrice ) AS fMinPrice  FROM vTrainPlan  "
			+ "WHERE DateDIFF(dStartDate,now()) between 1 and 90  GROUP BY iZoneInID ) b ON a.iZoneInID = b.iZoneInID "
			+ "AND a.fPrice = b.fMinPrice and a.iZoneInID>900000 and  a.iZoneOutID >900000 limit 10", nativeQuery = true)
	List<VTrainPlan> findOutByZoneOutName();

	/**
	 * 函数功能说明： 查询列车时刻 单程 往返一样
	 *
	 * @param 参数说明
	 * @return 返回值说明 jsp页面
	 * @throws 异常处理说明
	 * @author 创建人 shangzl 修改人hanxl
	 * @date 创建日期 修改时间 2021.03.17
	 */
	@Query(value = "select * from vTrainPlan where (:strOutCityName = \"\" or cZoneOutName like %:strOutCityName%) and (:strInCityName = \"\" or cZoneInName like %:strInCityName%) and DateDIFF(dStartDate,:outDate) between 0 and 1", nativeQuery = true)
	List<VTrainPlan> findSingle(@Param("strOutCityName") String strOutCityName,
			@Param("strInCityName") String strInCityName, @Param("outDate") Date outDate);

	/**
	 * 后台列车时刻信息管理中
	 *
	 * @author 创建人 hanxl 整理 修改人hanxl
	 * @date 创建日期 2020.11.19 修改时间 2021.03.17
	 */
	@Query(value = "select * from vTrainPlan where iTrainID=?1 ", nativeQuery = true)
	List<VTrainPlan> findAll(int iTrainID);

	/**
	 * 后台列车时刻信息管理中显示自己公司的列车时刻信息
	 *
	 * @author 创建人 hanxl 整理 修改人hanxl
	 * @param iPlaneID
	 * @date 创建日期 2020.11.19 修改时间 2021.03.17
	 */
	@Query(value = "select * from vTrainPlan where cSpare1=:iOrgID and iTrainID=:iTrainID ", nativeQuery = true)
	List<VTrainPlan> findByOrgID(@Param("iOrgID") int iOrgID, @Param("iTrainID") int iTrainID);

	/**
	 * 后台航班信息管理中超级管理员登录显示所有航班信息
	 *
	 * @author 创建人 hanxl 整理 修改人hanxl
	 * @param iPlaneID
	 * @date 创建日期 2020.11.19 修改时间 2021.03.17
	 */
	@Query(value = "select * from vTrainPlan where iTrainID=:iTrainID and (:strTrainOutZone = \"\" or cZoneOutName like %:strTrainOutZone%) "
			+ "and (:strTrainInZone = \"\" or cZoneInName like %:strTrainInZone%) and DateDIFF(dStartDate,:outDate) between 0 and 1  "
			+ "limit :startIndex,:count ", nativeQuery = true)
	List<VTrainPlan> findSearchALLDate(@Param("iTrainID") int iTrainID, @Param("outDate") Date outDate,
			@Param("strTrainOutZone") String strTrainOutZone, @Param("strTrainInZone") String strTrainInZone,
			@Param("startIndex") int startIndex, @Param("count") int count);

	/**
	 * 后台航班管理中显示自己公司的招标信息
	 *
	 * @author 创建人 hanxl 整理 修改人hanxl
	 * @date 创建日期 2020.11.19 修改时间 2021.02.22
	 */
	@Query(value = "select * from vTrainPlan where iTrainID=:iTrainID and (:strTrainOutZone = \"\" or cZoneOutName like %:strTrainOutZone%) "
			+ "and (:strTrainInZone = \"\" or cZoneInName like %:strTrainInZone%) and DateDIFF(dStartDate,:outDate) between 0 and 1  "
			+ "and cSpare1=:iOrgID limit :startIndex,:count", nativeQuery = true)
	List<VTrainPlan> findSearchByOrgIDDate(@Param("iTrainID") int iTrainID, @Param("iOrgID") int iOrgID,
			@Param("outDate") Date outDate, @Param("strTrainOutZone") String strTrainOutZone,
			@Param("strTrainInZone") String strTrainInZone, @Param("startIndex") int startIndex,
			@Param("count") int count);

	/**
	 * 后台航班信息管理中超级管理员登录显示所有航班信息
	 *
	 * @author 创建人 hanxl 整理 修改人hanxl
	 * @param iPlaneID
	 * @date 创建日期 2020.11.19 修改时间 2021.03.17
	 */
	@Query(value = "select * from vTrainPlan where iTrainID=:iTrainID and (:strTrainOutZone = \"\" or cZoneOutName like %:strTrainOutZone%) "
			+ "and (:strTrainInZone = \"\" or cZoneInName like %:strTrainInZone%) "
			+ "limit :startIndex,:count ", nativeQuery = true)
	List<VTrainPlan> findSearchALL(@Param("iTrainID") int iTrainID, @Param("strTrainOutZone") String strTrainOutZone,
			@Param("strTrainInZone") String strTrainInZone, @Param("startIndex") int startIndex,
			@Param("count") int count);

	/**
	 * 后台航班管理中显示自己公司的招标信息
	 *
	 * @author 创建人 hanxl 整理 修改人hanxl
	 * @date 创建日期 2020.11.19 修改时间 2021.02.22
	 */
	@Query(value = "select * from vTrainPlan where iTrainID=:iTrainID and (:strTrainOutZone = \"\" or cZoneOutName like %:strTrainOutZone%) "
			+ "and (:strTrainInZone = \"\" or cZoneInName like %:strTrainInZone%)  "
			+ "and cSpare1=:iOrgID limit :startIndex,:count", nativeQuery = true)

	List<VTrainPlan> findSearchByOrgID(@Param("iTrainID") int iTrainID, @Param("iOrgID") int iOrgID,
			@Param("strTrainOutZone") String strTrainOutZone, @Param("strTrainInZone") String strTrainInZone,
			@Param("startIndex") int startIndex, @Param("count") int count);

	/**
	 * 后台列车时刻信息管理中超级管理员登录显示所有公司的列车时刻信息 获取分页
	 *
	 * @author 创建人 hanxl 整理 修改人hanxl
	 * @param iPlaneID
	 * @date 创建日期 2020.11.19 修改时间 2021.02.22
	 */
	@Query(value = "select count(*)  from vTrainPlan where iTrainID=:iTrainID and (:strTrainOutZone = \"\" or cZoneOutName like %:strTrainOutZone%) "
			+ "and (:strTrainInZone = \"\" or cZoneInName like %:strTrainInZone%) and DateDIFF(dStartDate,:outDate) between 0 and 1  ", nativeQuery = true)
	int countALLDate(@Param("iTrainID") int iTrainID, @Param("outDate") Date outDate,
			@Param("strTrainOutZone") String strTrainOutZone, @Param("strTrainInZone") String strTrainInZone);

	/**
	 * 后台航班管理中显示自己公司的航班信息 获取分页
	 *
	 * @author 创建人 hanxl 整理 修改人hanxl
	 * @param iOrgID2
	 * @date 创建日期 2020.11.19 修改时间 2021.02.22
	 */
	@Query(value = "select count(*)  from vTrainPlan where iTrainID=:iTrainID and (:strTrainOutZone = \"\" or cZoneOutName like %:strTrainOutZone%) "
			+ "and (:strTrainInZone = \"\" or cZoneInName like %:strTrainInZone%) and DateDIFF(dStartDate,:outDate) between 0 and 1  "
			+ "and cSpare1=:iOrgID", nativeQuery = true)
	int countByOrgIDDate(@Param("iTrainID") int iTrainID, @Param("iOrgID") int iOrgID, @Param("outDate") Date outDate,
			@Param("strTrainOutZone") String strTrainOutZone, @Param("strTrainInZone") String strTrainInZone);

	/**
	 * 后台航班信息管理中超级管理员登录显示所有公司的航班信息 获取分页
	 *
	 * @author 创建人 hanxl 整理 修改人hanxl
	 * @param iPlaneID
	 * @date 创建日期 2020.11.19 修改时间 2021.02.22
	 */
	@Query(value = "select count(*)  from vTrainPlan where iTrainID=:iTrainID and (:strTrainOutZone = \"\" or cZoneOutName like %:strTrainOutZone%) "
			+ "and (:strTrainInZone = \"\" or cZoneInName like %:strTrainInZone%)  ", nativeQuery = true)
	int countALL(@Param("iTrainID") int iTrainID, @Param("strTrainOutZone") String strTrainOutZone,
			@Param("strTrainInZone") String strTrainInZone);

	/**
	 * 后台航班管理中显示自己公司的航班信息 获取分页
	 *
	 * @author 创建人 hanxl 整理 修改人hanxl
	 * @param iOrgID2
	 * @date 创建日期 2020.11.19 修改时间 2021.02.22
	 */
	@Query(value = "select count(*)  from vTrainPlan where iTrainID=:iTrainID and (:strTrainOutZone = \"\" or cZoneOutName like %:strTrainOutZone%) "
			+ "and (:strTrainInZone = \"\" or cZoneInName like %:strTrainInZone%) "
			+ "and cSpare1=:iOrgID", nativeQuery = true)

	int countByOrgID(@Param("iTrainID") int iTrainID, @Param("iOrgID") int iOrgID,
			@Param("strTrainOutZone") String strTrainOutZone, @Param("strTrainInZone") String strTrainInZone);

}
